package com.jiepos.mpos.biz.transaction.service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.jiepos.mpos.biz.user.service.AppInsInfService;
import com.jiepos.mpos.common.entity.App_Ins_InfEntity;
import com.jiepos.mpos.core.util.DateUtil;
import com.jiepos.mpos.core.util.StringUtils;

import oracle.jdbc.driver.OracleTypes;

/**
 * @ClassName: TransSummaryService
 * @Description: 交易分组汇总服务
 * @author 孙永
 * @date 2017年11月7日 下午6:37:50
 */
@Service
public class TransSummaryService {
	
	@Value("${summary.file.save.path}")
	private String summaryFilePath;
	
	@Autowired
	private AppInsInfService appInsInfService;
	@Autowired
	protected JdbcTemplate jdbcTemplate;

	/**
	 * 根据指定日期范围生成汇总文件，文件存放于当前系统日期命名的文件夹
	 * @param beginDate 开始日期时间
	 * @param endDate 结束日期时间
	 */
	public void transSummary(String beginDate, String endDate){
		/*检测是否为工作日*/
		if(!isWorkDay()){
			return;
		}
		/*汇总日期*/
		String transDay = DateUtil.getCurrDateStr();
		transSummary(beginDate, endDate, transDay);
	}
	
	/**
	 * 根据制定日期时间范围生成汇总文件
	 * @param beginDate 开始日期时间
	 * @param endDate 结束日期时间
	 * @param transDay 汇总日期(月份)，文件夹名称
	 */
	public void transSummary(String beginDate, String endDate, String transDay){
		/*递归获取机构*/
		List<App_Ins_InfEntity> insInfs = appInsInfService.queryAppIns();
		/*统计渠道交易量*/
		List<Map<String, Object>> sumChannels = null, sumFails = null, sumCustoms = null, sumCustomChannels = null, sumIns = null;
		for(App_Ins_InfEntity insInf : insInfs){
			/*顶级机构*/
			if(StringUtils.isBlank(insInf.getSup_ins_uuid())){
				/*统计渠道交易量*/
				sumChannels = summaryByChannel(insInf.getPhone(), beginDate, endDate);
				/*统计差错交易量*/
				sumFails = summaryFailByChannel(insInf.getPhone(), beginDate, endDate);
				/*统计会员交易量分渠道*/
				sumCustomChannels = summaryByCustomAndChannel(insInf.getPhone(), beginDate, endDate);
			}
			/*统计会员交易量不分渠道*/
			sumCustoms = summaryByCustom(insInf.getPhone(), beginDate, endDate);
			/*统计当前机构的交易量*/
			sumIns = summaryByIns(insInf.getIns_cd(), beginDate, endDate);
			/*统计当前机构的直属下级交易量*/
			List<App_Ins_InfEntity> children = appInsInfService.queryListBySupUuid(insInf.getIns_uuid());
			if(children != null && !children.isEmpty()){
				for(App_Ins_InfEntity entity : children){
					List<Map<String, Object>> summaryChildren = summaryByIns(entity.getIns_cd(), beginDate, endDate);
					if(summaryChildren != null && !summaryChildren.isEmpty()){
						sumIns.addAll(summaryChildren);
					}
				}
			}
			/*生成excel文件*/
			createExcel(insInf.getSup_ins_uuid(), transDay, insInf.getAddr(), insInf.getIns_cd(), 
					sumChannels, sumIns, sumCustoms, sumCustomChannels, sumFails);
		}
	}
	
	/**
	 * 渠道清算交易汇总
	 * @param phone 机构手机号
	 * @param beginDate 开始日期时间
	 * @param endDate 结束日期时间
	 * @return
	 */
	private List<Map<String, Object>> summaryByChannel(String phone, String beginDate, String endDate){
		return queryList("PACKAGE_SUMMARY.P_SUMMARY_CHANNEL_SETTLE", new Object[]{phone, beginDate, endDate});
	}
	
	/**
	 * 机构清算交易汇总
	 * @param insNo 机构编号
	 * @param beginDate 开始日期时间
	 * @param endDate 结束日期时间
	 * @return
	 */
	private List<Map<String, Object>> summaryByIns(String insNo, String beginDate, String endDate){
		return queryList("PACKAGE_SUMMARY.P_SUMMARY_INS_SETTLE", new Object[]{insNo, beginDate, endDate});
	}
	
	/**
	 * 会员清算交易汇总不分渠道
	 * @param phone 机构手机号
	 * @param beginDate 开始日期时间
	 * @param endDate 结束日期时间
	 * @return
	 */
	private List<Map<String, Object>> summaryByCustom(String phone, String beginDate, String endDate){
		return queryList("PACKAGE_SUMMARY.P_SUMMARY_CUSTOM_SETTLE", new Object[]{phone, beginDate, endDate});
	}
	
	/**
	 * 会员清算交易汇总分渠道
	 * @param phone 机构手机号
	 * @param beginDate 开始日期时间
	 * @param endDate 结束日期时间
	 * @return
	 */
	private List<Map<String, Object>> summaryByCustomAndChannel(String phone, String beginDate, String endDate){
		return queryList("PACKAGE_SUMMARY.P_SUMMARY_CUSTOM_CNL_SETTLE", new Object[]{phone, beginDate, endDate});
	}
	
	/**
	 * 渠道清算差错流水汇总
	 * @param phone 机构手机号
	 * @param beginDate 开始日期时间
	 * @param endDate 结束日期时间
	 * @return
	 */
	private List<Map<String, Object>> summaryFailByChannel(String phone, String beginDate, String endDate){
		return queryList("PACKAGE_SUMMARY.P_SUMMARY_CHANNEL_FAIL", new Object[]{phone, beginDate, endDate});
	}
	
	/**
	 * 存过
	 * @param pName 存过名称
	 * @param params 入参
	 * @return 结果集
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	private List<Map<String, Object>> queryList(final String pName, final Object...params){
		List<Map<String, Object>> result = (List<Map<String, Object>>) jdbcTemplate.execute(new CallableStatementCreator(){
			@Override
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				StringBuffer sql = new StringBuffer();
				sql.append("{call ").append(pName).append("(");
				/*根据入参数量，组装占位符个数*/
				if(params != null && params.length > 0){
					for(int i=0;i<params.length;i++){
						sql.append("?,");
					}
				}
				/*最后一个占位符为结果集*/
				sql.append("?)}");
				CallableStatement cs = con.prepareCall(sql.toString());
				int i = 1;
				if(params != null && params.length > 0){
					for(Object param : params){
						cs.setObject(i++, param);
					}
				}
				cs.registerOutParameter(i, OracleTypes.CURSOR);
				return cs;
			}
		}, new CallableStatementCallback(){
			@Override
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				List<Map<String, Object>> resultMap = new ArrayList<>();
				cs.execute();
				ResultSet rs = (ResultSet) cs.getObject(params.length + 1);
				if(rs != null){
					ResultSetMetaData data = rs.getMetaData();
					int colums = data.getColumnCount();
					while(rs.next()){
						/*有序集合，按照存过结果，有序排列列名*/
						Map<String, Object> rowMap = new LinkedHashMap<>();
						for(int i=1;i<=colums;i++){
							rowMap.put(data.getColumnName(i), rs.getObject(i));
						}
						resultMap.add(rowMap);
					}
				}
				return resultMap;
			}
		});
		return result;
	}
	
	/**
	 * 验证当前是否为工作日
	 * @return
	 */
	private boolean isWorkDay(){
		String sql = "SELECT COUNT(1) FROM T_APP_REST_DAY WHERE REST_DATE = TO_CHAR(SYSDATE, 'YYYYMMDD')";
		Number number = jdbcTemplate.queryForObject(sql, Integer.class);
		if(number != null && number.intValue() == 0){
			return true;
		}
		return false;
	}
	
	/**
	 * 创建表格
	 * @param supInsUuid 父级机构uuid
	 * @param transDay 汇总日期
	 * @param path 机构路径
	 * @param insNo 机构编号
	 * @param sumChannels 渠道清算汇总数据
	 * @param sumIns 机构汇总数据
	 * @param sumCustoms 会员不分渠道汇总数据
	 * @param sumCustomChannels 会员分渠道汇总数据
	 * @param sumFails 渠道差错交易汇总数据
	 */
	private void createExcel(String supInsUuid, String transDay, String path, String insNo, 
			List<Map<String, Object>> sumChannels, 
			List<Map<String, Object>> sumIns, 
			List<Map<String, Object>> sumCustoms,
			List<Map<String, Object>> sumCustomChannels,
			List<Map<String, Object>> sumFails){
		FileOutputStream out = null;
		try{
			SXSSFWorkbook book = new SXSSFWorkbook(1000);
			/*顶级机构拥有的sheet页*/
			if(StringUtils.isBlank(supInsUuid)){
				createSheet(book, SHEET_NAME[0], CHANNEL_SUMMARY_COLUMN, sumChannels);
			}
			/*非顶级机构拥有的sheet页*/
			Object[][] insColumns = changeData(supInsUuid, INS_SUMMARY_COLUMN, sumIns);
			Object[][] customColumns = changeData(supInsUuid, CUSTOM_SUMMARY_COLUMN, sumCustoms);
			createSheet(book, SHEET_NAME[1], insColumns, sumIns);
			createSheet(book, SHEET_NAME[2], customColumns, sumCustoms);
			/*顶级机构拥有的sheet页*/
			if(StringUtils.isBlank(supInsUuid)){
				createSheet(book, SHEET_NAME[3], CUSTOM_CHANNEL_SUMMARY_COLUMN, sumCustomChannels);
				createSheet(book, SHEET_NAME[4], CHANNEL_FAIL_COLUMN, sumFails);
			}
			/*写入文件*/
			File file = new File(summaryFilePath + transDay + path);
			if(file != null && !file.exists()){
				file.mkdirs();
			}
			out = new FileOutputStream(file.getPath() + "/" + insNo + ".xlsx");
			book.write(out);
			out.close();
			book.dispose();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(out != null){
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	/**
	 * 创建表格sheet页
	 * @param book
	 * @param sheetname sheet页名称
	 * @param columns 列头数组
	 * @param datas 数据集合
	 */
	private void createSheet(SXSSFWorkbook book, String sheetname, Object[][] columns, List<Map<String, Object>> datas){
		Sheet sheet = book.createSheet(sheetname);
		/*设置列宽*/
		for(int i=0;i<columns.length;i++){
			sheet.setColumnWidth(i, 256 * (int)columns[i][1]);
		}
		CellStyle cellStyle = getDataStyle(book, HSSFColor.GREY_25_PERCENT.index);
		int rowIndex = 0;
		Row row = sheet.createRow(rowIndex);
		/*设置列头*/
		for(int i=0;i<columns.length;i++){
			Cell cell = row.createCell(i);
			cell.setCellType(Cell.CELL_TYPE_STRING);
			cell.setCellValue(columns[i][0].toString());
			cell.setCellStyle(cellStyle);
		}
		/*填充数据*/
		if(datas != null && !datas.isEmpty()){
			rowIndex ++;
			cellStyle = getDataStyle(book, HSSFColor.WHITE.index);
			Map<String, Object> total = new LinkedHashMap<>();
			for(Map<String, Object> data : datas){
				Row dataRow = sheet.createRow(rowIndex);
				Set<String> keys = data.keySet();
				int i = 0;
				for (Iterator<String> iterator = keys.iterator(); iterator.hasNext();) {
					String key = (String) iterator.next();
					String value = data.get(key) != null ? data.get(key).toString() : "";
					/*统计数据*/
					if((boolean) columns[i][2]){
						BigDecimal sum = total.get(key) != null ? new BigDecimal(total.get(key).toString()) : BigDecimal.ZERO;
						total.put(key, sum.add(StringUtils.isBlank(value) ? BigDecimal.ZERO : new BigDecimal(value)).setScale(2, BigDecimal.ROUND_HALF_UP).toString());
					}else{
						total.put(key, null);
					}
					Cell cell = dataRow.createCell(i++, Cell.CELL_TYPE_STRING);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle);
				}
				rowIndex ++;
			}
			/*创建总计行*/
			Row dataRow = sheet.createRow(rowIndex);
			Set<String> keys = total.keySet();
			int i = 0;
			for (Iterator<String> iterator = keys.iterator(); iterator.hasNext();) {
				String key = (String) iterator.next();
				Cell cell = dataRow.createCell(i++, Cell.CELL_TYPE_STRING);
				cell.setCellValue(total.get(key) != null ? total.get(key).toString() : "");
				cell.setCellStyle(cellStyle);
			}
		}
	}
	
	/**
	 * 转换列和数据
	 * @param supInsUuid 父级机构
	 * @param columns 列
	 * @param sumData 数据
	 * @return
	 */
	private Object[][] changeData(String supInsUuid, Object[][] columns, List<Map<String, Object>> sumData){
		if(StringUtils.isBlank(supInsUuid)){
			return columns;
		}
		Object[][] newColumns = new Object[columns.length-1][];
		int index = -1, _index = 0;
		for(int i=0;i<columns.length;i++){
			if(columns[i].length == 3){
				newColumns[_index++] = columns[i];
			}else{
				index = i;
			}
		}
		if(sumData != null && !sumData.isEmpty()){
			for(Map<String, Object> data : sumData){
				Set<String> keys = data.keySet();
				int i = 0;
				for (Iterator<String> iterator = keys.iterator(); iterator.hasNext();) {
					if(i++ == index){
						String key = (String) iterator.next();
						data.remove(key);
					}
				}
			}
		}
		return newColumns;
	}
	
	// 表格数据格式
	private CellStyle getDataStyle(SXSSFWorkbook workbook, short bg) {
		// 设置字体
		Font font = workbook.createFont();
		// 设置字体名字
		font.setFontName("Courier New");
		// 设置样式;
		CellStyle style = workbook.createCellStyle();
		// 设置底边框;
		style.setBorderBottom(CellStyle.BORDER_THIN);
		// 设置底边框颜色;
		style.setBottomBorderColor(HSSFColor.BLACK.index);
		// 设置左边框;
		style.setBorderLeft(CellStyle.BORDER_THIN);
		// 设置左边框颜色;
		style.setLeftBorderColor(HSSFColor.BLACK.index);
		// 设置右边框;
		style.setBorderRight(CellStyle.BORDER_THIN);
		// 设置右边框颜色;
		style.setRightBorderColor(HSSFColor.BLACK.index);
		// 设置顶边框;
		style.setBorderTop(CellStyle.BORDER_THIN);
		// 设置顶边框颜色;
		style.setTopBorderColor(HSSFColor.BLACK.index);
		// 在样式用应用设置的字体;
		style.setFont(font);
		// 设置背景颜色
		style.setFillForegroundColor(bg);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		// 设置水平对齐的样式为居中对齐;
		style.setAlignment(CellStyle.ALIGN_LEFT);
		// 设置垂直对齐的样式为居中对齐;
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		return style;
	}
	
	/*表头设置
	 * 第一个是列名
	 * 第二个是列宽度
	 * 第三个是否为统计列*/
	/** 渠道交易汇总数据列头*/
	private static final Object[][] CHANNEL_SUMMARY_COLUMN = 
		{
			{"清算日期", 11, false},
			{"渠道编号", 23, false},
			{"渠道名称", 23, false},
			{"交易笔数", 12, true},
			{"交易总额", 12, true},
			{"会员手续费", 12, true},
			{"成本手续费", 12, true},
			{"毛利", 12, true}
		};
	
	/** 机构清算交易汇总*/
	private static final Object[][] INS_SUMMARY_COLUMN = 
		{
			{"清算日期", 11, false},
			{"机构号", 10, false},
			{"机构名称", 25, false},
			{"业务名称", 20, false},
			{"业务参数名称", 23, false},
			{"交易笔数", 12, false},
			{"交易金额", 12, false},
			{"会员手续费", 12, false},
			{"成本手续费", 12, false, null},
			{"结算手续费", 12, false},
			{"会员结算手续费", 15, false},
			{"毛利", 12, false},
			{"净利", 12, false}
		};
	
	/** 会员清算交易汇总*/
	private static final Object[][] CUSTOM_SUMMARY_COLUMN = 
		{
			{"清算日期", 11, false},
			{"会员编号", 23, false},
			{"会员手机", 15, false},
			{"会员姓名", 15, false},
			{"机构号", 10, false},
			{"机构名称", 25, false},
			{"业务名称", 20, false},
			{"业务参数名称", 23, false},
			{"交易笔数", 12, true},
			{"交易金额", 12, true},
			{"会员手续费", 12, true},
			{"成本手续费", 12, true, null},
			{"会员结算手续费", 15, true},
			{"毛利", 15, true}
		};
	
	/** 会员清算交易汇总分渠道*/
	private static final Object[][] CUSTOM_CHANNEL_SUMMARY_COLUMN = 
		{
			{"清算日期", 11, false},
			{"会员编号", 23, false},
			{"会员手机", 15, false},
			{"会员姓名", 15, false},
			{"机构号", 10, false},
			{"机构名称", 25, false},
			{"业务名称", 20, false},
			{"业务参数名称", 23, false},
			{"交易笔数", 12, true},
			{"交易金额", 12, true},
			{"会员手续费", 12, true},
			{"成本手续费", 12, true},
			{"结算手续费", 12, true},
			{"会员结算手续费", 15, true},
			{"毛利", 12, true},
			{"净利", 12, true},
			{"渠道", 33, false}
		};
	
	/** 渠道差错流水汇总数据列头*/
	private static final Object[][] CHANNEL_FAIL_COLUMN = 
		{
			{"交易时间",23, false},
			{"会员编号",23, false},
			{"手机号码",15, false},
			{"会员姓名",15, false},
			{"本地流水号",23, false},
			{"支付账号",23, false},
			{"交易金额",12, true},
			{"商户手续费",12, true},
			{"清算金额",12, true},
			{"渠道商户号",38, false},
			{"渠道终端号",15, false},
			{"渠道流水号",20, false},
			{"渠道编号",23, false},
			{"渠道名称",23, false},
			{"返回码",11, false},
			{"返回描述",23, false},
			{"清算日期",11, false},
			{"清算状态",11, false},
			{"清算状态描述",23, false}
		};
	
	/*sheet页名称*/
	private static final String[] SHEET_NAME = {
			"渠道清算交易汇总",
			"机构清算交易汇总",
			"会员清算交易汇总",
			"会员渠道清算交易汇总",
			"渠道清算差错流水汇总"
	};
}
